﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using QuanLyThuVien.DTO;
using System.Data;
using System.Windows.Forms;
namespace QuanLyThuVien.DAO
{
    class ChiTietPhieuMuonDAO
    {
        public static void ChiTietMuon(ChiTietPhieuMuonDTO x)
        {
            NorthwindDataContext QLTV = new NorthwindDataContext();
            ChiTietPhieuMuon c = new ChiTietPhieuMuon();
            c.MaSach = x.MaSach1;
            c.MaPhieuMuon = x.MaPhieuMuon1;
            c.TinhTrangTra = x.TinhTrangTra1;
            QLTV.ChiTietPhieuMuons.InsertOnSubmit(c);
            QLTV.SubmitChanges();   
        }

        public static DataTable LayMaCTPM(int MaDG)
        {
            NorthwindDataContext cnn = new NorthwindDataContext();
            var sql = from pm in cnn.PhieuMuons
                      join ct in cnn.ChiTietPhieuMuons on pm.MaPhieuMuon equals ct.MaPhieuMuon
                      where pm.MaDocGia == MaDG
                      select new { ct.MaChiTietPhieuMuon};
            DataTable dt = new DataTable();
           // dt.Columns.Add("MaSach", typeof(int));
            dt.Columns.Add("MaChiTietPhieuMuon", typeof(int));
            foreach (var gan in sql)
            {
                dt.Rows.Add(gan.MaChiTietPhieuMuon);
            }
            return dt;
        }

        public static int LayMaCTPMuon(int MaDG, int MaSach)
        {
            NorthwindDataContext cnn = new NorthwindDataContext();
            var sql = (from pm in cnn.PhieuMuons
                        join ctiet in cnn.ChiTietPhieuMuons on pm.MaPhieuMuon equals ctiet.MaPhieuMuon
                        where pm.MaDocGia == MaDG && ctiet.MaSach == MaSach
                        select ctiet.MaChiTietPhieuMuon).Max();
            return sql;
        }

        //phan them vo_Truyen
        public static DataTable DS_SachMuon(int madg)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            //lay ra tat ca cac cuon sach doc gia muon ma chua tra
            var ds = from pm in db.PhieuMuons
                     join ctpm in db.ChiTietPhieuMuons on pm.MaPhieuMuon equals ctpm.MaPhieuMuon
                     where pm.MaDocGia == madg && ctpm.TinhTrangTra == 0
                     select new
                     {
                         ctpm.MaSach,
                         pm.NgayMuon,
                     };
            int dem_SachChuaTra = ds.Count();
            // lay ra ds cac cuon sach qua han
            var ds_quahan = from gtri in ds
                            where (DateTime.Now.Date - gtri.NgayMuon.Date).TotalDays > 4
                            select gtri;
            int dem_SachQuaHan = ds_quahan.Count();

            DataTable dt = new DataTable();
            dt.Columns.Add("Count_SachChuaTra", typeof(int));
            dt.Columns.Add("Count_SachQuaHan", typeof(int));
            dt.Rows.Add(dem_SachChuaTra, dem_SachQuaHan);
            return dt;

        }

        //lay ra ds cac cuon sach da muon nhung chua tra
        public static DataTable DS_SachMuonChuaTra(int madg)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            //lay ra tat ca cac cuon sach doc gia muon ma chua tra
            var ds = from pm in db.PhieuMuons
                     join ctpm in db.ChiTietPhieuMuons on pm.MaPhieuMuon equals ctpm.MaPhieuMuon
                     where pm.MaDocGia == madg && ctpm.TinhTrangTra == 0
                     select new
                     {
                         ctpm.MaSach,
                         pm.NgayMuon,

                     };
            DataTable dt = new DataTable();
            dt.Columns.Add("MaSach", typeof(int));
            dt.Columns.Add("NgayMuon", typeof(DateTime));
            foreach (var gtri in ds)
            {
                dt.Rows.Add(gtri.MaSach, gtri.NgayMuon);
            }
            return dt;

        }
        
        //cap nhat tinh trang tra 
        public static void CapNhat_TinhTrangTra(int ma)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var ctpm = db.ChiTietPhieuMuons.Single(ct => ct.MaChiTietPhieuMuon == ma);
            ctpm.TinhTrangTra = 1;
            db.SubmitChanges();
        }

        //lay tinh trang tra cua CTPM
        public static int TT_Tra(int ma)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var ds = from ct in db.ChiTietPhieuMuons
                     where ct.MaChiTietPhieuMuon == ma
                     select ct;
            DataTable dt = new DataTable();
            dt.Columns.Add("TinhTrangTra", typeof(int));
            foreach(var gtri in ds)
            {
                dt.Rows.Add(gtri.TinhTrangTra);
            }
            int tt= int.Parse(dt.Rows[0]["TinhTrangTra"].ToString());
            return tt;

        }


        //trung
        public static DataGridView CTPMketPM()
        {
            NorthwindDataContext db = new NorthwindDataContext();
            DataGridView dt = new DataGridView();
            var sql = from CTPM in db.ChiTietPhieuMuons
                      join PM in db.PhieuMuons on CTPM.MaPhieuMuon equals PM.MaPhieuMuon
                      join DG in db.DocGias on PM.MaDocGia equals DG.MaDocGia
                      join X in db.Saches on CTPM.MaSach equals X.MaSach
                      select new
                      {
                          X.TenSach,
                          X.TriGia,
                          DG.HoTenDocGia,
                          CTPM.MaChiTietPhieuMuon,
                          CTPM.MaSach,
                          CTPM.MaPhieuMuon,
                          PM.MaDocGia,
                          PM.NgayMuon,
                      };
            dt.DataSource = sql;
            return dt;
        }


        // trung
        public static DataTable DS_SachMuonChuaTra1(int madg)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            //lay ra tat ca cac cuon sach doc gia muon ma chua tra
            var ds = from pm in db.PhieuMuons
                     join ctpm in db.ChiTietPhieuMuons on pm.MaPhieuMuon equals ctpm.MaPhieuMuon
                     join s in db.Saches on ctpm.MaSach equals s.MaSach
                     where pm.MaDocGia == madg && ctpm.TinhTrangTra == 0
                     select new
                     {
                         s.TenSach,
                         ctpm.MaSach,
                         s.TriGia,
                         pm.NgayMuon,
                         ctpm.MaChiTietPhieuMuon,
                     };

            DataTable dt = new DataTable();
            dt.Columns.Add("Tên Sách", typeof(string));
            dt.Columns.Add("Mã Sách", typeof(int));
            dt.Columns.Add("Trị Giá", typeof(int));
            dt.Columns.Add("Ngày Mượn", typeof(DateTime));
            dt.Columns.Add("Ma CTPM", typeof(int));
            foreach (var gtri in ds)
            {
                dt.Rows.Add(gtri.TenSach, gtri.MaSach, gtri.TriGia, gtri.NgayMuon, gtri.MaChiTietPhieuMuon);
            }
            return dt;

        }
     
    }

}
